﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using IToursDataService;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using IToursDataService.Models;
using IToursDataService.Helpers;
using System.Threading;

namespace ToursDataService
{
    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码、svc 和配置文件中的类名“TeamGroupCtrl”。
    public class TeamGroupCtrl : ITeamGroupCtrl
    {
        #region 团有关数据获取

         /// <summary>
        /// 根据团号获取组团人A
        /// </summary>
        /// <param name="tourgroupid"></param>
        /// <returns></returns>
        public string GetTeamGroupOwnerAByTourgroupID(string tourgroupid)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT OwnerA FROM orgt_tourgroup WHERE tourgroupid='{0}'", tourgroupid);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        /// <summary>
        /// 根据团号获取团名
        /// </summary>
        /// <returns></returns>
        public  string GetTeamGroupNameById(string tourgroupid)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT TourgroupName FROM orgt_tourgroup where tourgroupid='{0}'", tourgroupid);
            dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            if (obj == DBNull.Value || obj == null)
                return "";
            else
                return obj.ToString();
        }

        /// <summary>
        /// 根据团号获取部门Email
        /// </summary>
        /// <returns></returns>
        public  string GetDepartmentEmailById(string tourgroupid)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT bas_Department.MailServer FROM bas_Department,orgt_tourgroup where orgt_tourgroup.tourgroupid='{0}' and orgt_tourgroup.DepartmentID = bas_Department.DepartmentID", tourgroupid);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        public  TeamStateEnum GetTourGroupState(string tourGroupID)
        {
            if (tourGroupID == "") return TeamStateEnum.全部;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format("SELECT TourgroupState FROM orgt_tourgroup WHERE TourgroupID='{0}'", tourGroupID);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            if (obj == DBNull.Value || obj == null)
                return TeamStateEnum.全部;
            else
                return EnumHelper.Team_Str2Enum(obj.ToString(), true);
        }
        /// <summary>
        /// 获取指定团的房间备注信息
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        public  string GetTeamGroupRoomRemark(string tourGroupID)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT RoomRemark FROM orgt_tourgroup WHERE TourGroupID = @tourGroupID";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupID);

            return db.ExecuteScalar(dbCommand).ToString();

        }
        /// <summary>
        /// 根据指定计划号获取团的国家代码
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        public  string GetCountryNameForTeamGroup(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string sql = "SELECT Country FROM orgt_tourgroup WHERE TourGroupID = @tourGroupID";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourgroupId);
            return db.ExecuteScalar(dbCommand).ToString();
        }
        /// <summary>
        /// 获取系统中所有团的团号/计划号
        /// </summary>
        /// <returns></returns>
        public  DataSet GetAllIDForTeamGroup()
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT tourgroupid FROM orgt_tourgroup";
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteDataSet(dbCommand);
        }

        /// <summary>
        ///  根据给定的客户名称检索客户所在团信息
        /// </summary>
        /// <param name="guestName"></param>
        /// <returns></returns>
        public  DataTable GetTeamGroupData(string guestName, int providerLoginID)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT t.* FROM orgt_TourGroup t,orgt_TourGroupSchedule s 
                                         WHERE  t.TourgroupID in (SELECT tourgroupid FROM orgt_guestlist WHERE [Name] like '%'+@GuestName+'%')
	                                     AND t.TourgroupID=s.TourgroupID AND s.TravelAgencyID=@ProviderID AND (t.TourgroupState=@TourgroupState1 OR t.TourgroupState=@TourgroupState2 OR t.TourgroupState=@TourgroupState3 OR t.TourgroupState=@TourgroupState4) ");
            dbCommand = db.GetSqlStringCommand(sql);
            db.AddParameter(dbCommand, "@TourgroupState1", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, TeamStateEnum.成行);
            db.AddParameter(dbCommand, "@TourgroupState2", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, TeamStateEnum.审核);
            db.AddParameter(dbCommand, "@TourgroupState3", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, TeamStateEnum.终结);
            db.AddParameter(dbCommand, "@TourgroupState4", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, TeamStateEnum.分配);
            db.AddParameter(dbCommand, "@ProviderID", DbType.Int32, ParameterDirection.Input, "", DataRowVersion.Default, providerLoginID);
            db.AddParameter(dbCommand, "@GuestName", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, guestName);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 用于PAD客户端获取与导游有关的团信息，此函数视为导游登录系统成功后，以登录的用户名为准
        /// </summary>
        /// <param name="ciceroniLoginUserName">导游登录用户名，与aspnet_UserInfo、bas_Ciceroni表中UserName字段对应</param>
        /// <returns></returns>
        public DataTable GetTeamGroupData(string ciceroniLoginUserName)
        {

            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder szCondition = new StringBuilder();
            String Sql = string.Format(@"select t.TourgroupID,t.TourgroupName,t.StartDate,t.EndDate from orgt_TourGroup t,cic_GuideArrange g,bas_Ciceroni c WHERE c.UserName='{0}' AND g.CiceroniID=c.CiceroniID and g.TourgroupID=t.TourgroupID and t.TourgroupState='{1}'",ciceroniLoginUserName, TeamStateEnum.成行);

            DbCommand dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public DataTable GetTeamGroupDataForRestaurantAndBillBatchCopy(string loginProviderId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder szCondition = new StringBuilder();
            String Sql = string.Format(@"SELECT t.TourgroupID,t.TourgroupName,t.TourgroupState FROM orgt_TourGroup t,orgt_TourGroupSchedule s WHERE t.TourgroupID=s.TourgroupID AND TourgroupState<>'{0}' and TourgroupState<>'{1}' and TourgroupState<>'{2}' and TourgroupState<>'{3}' AND s.TravelAgencyID={4}", TeamStateEnum.取消, TeamStateEnum.删除, TeamStateEnum.审核, TeamStateEnum.终结, loginProviderId);

            DbCommand dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据条件获取团数据
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="teamstate"></param>
        /// <param name="username">我的团（当前登录用户）</param>
        /// <param name="departmentId"></param>
        /// <returns></returns>
        public  DataTable GetTeamGroupData(DateTime start, DateTime end, string loginProviderId, string ownerAB)
        {

            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder szCondition = new StringBuilder();

            if (loginProviderId != "")//我的团条件，根据当前登录人判定
                szCondition.AppendFormat("s.TravelAgencyID={0}", loginProviderId);
            else
                return null;
            if (szCondition.Length != 0)//按着组团人
            {
                if (ownerAB != "")
                    szCondition.AppendFormat(" AND (OwnerA='{0}' OR OwnerB='{0}')", ownerAB);
            }
            else
            {
                if (ownerAB != "")
                    szCondition.AppendFormat(" (OwnerA='{0}' OR OwnerB='{0}')", ownerAB);
            }

            String Sql = string.Format(@"SELECT t.* from orgt_TourGroup t,orgt_TourGroupSchedule s WHERE t.TourgroupID=s.TourgroupID AND (TourgroupState = '{0}' OR TourgroupState = '{1}' OR TourgroupState = '{2}') AND StartDate >='{3}' AND StartDate<='{4}'", TeamStateEnum.分配, TeamStateEnum.审核, TeamStateEnum.终结, start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"));


            if (szCondition.Length != 0)
                Sql += " AND " + szCondition.AppendFormat(" ORDER BY StartDate");
            else
                Sql += " ORDER BY StartDate";

            DbCommand dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据条件获取团数据
        /// </summary>
        /// <param name="hasGuide">false,全部，true,无导游</param>
        /// <param name="tourgroupId"></param>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public  DataTable GetTeamGroupData(string tourGroupId, string dateMonth, string loginProviderId)//,string endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string szCondition = "";

            if (tourGroupId != "")
                szCondition = "t.TourGroupID='" + tourGroupId + "'";

            /*
                描述：通过条件过滤出属于当前登录供应商的团
                        1、通过大行程中TravelAgencyID确定所属‘登录’供应商的团
                        2、通过导游安排表进一步过滤属于登录供应商的团，通过导游安排表中OwnerProviderID找到属于登录供应商的已安排的团或未安排为NULL的团
                        注：个人理解Where条件过滤过程中，括号外的条件与括号内两个or条件是分别进行组合过滤的。
                        其中g.OwnerProviderID IS NULL语句原本是g.CiceroniName IS NULL，效果一样，为了美观采用OwnerProviderID
            */
            //          string Sql = string.Format(@"SELECT t.TourgroupID, t.TourgroupName, t.StartDate, t.EndDate, t.OwnerA, t.StayOver, t.AdultNum, t.ChildNum, g.CiceroniName FROM orgt_TourGroup t LEFT JOIN cic_GuideArrange g ON t.TourgroupID = g.TourgroupID
            //                                         WHERE DATEDIFF(month,t.StartDate,'{0}')=0  AND HasGuide=1 AND t.TourgroupState<>'{1}'AND t.TourgroupState<>'{2}' AND t.TourgroupState<>'{3}'", dateMonth, TeamStateEnum.取消, TeamStateEnum.删除, TeamStateEnum.计划);
            //            string Sql = string.Format(@"SELECT DISTINCT t.TourgroupID, t.TourgroupName, t.StartDate, t.EndDate, t.OwnerA, t.StayOver, t.AdultNum, t.ChildNum, g.CiceroniName 
            //                                         FROM orgt_TourGroup t LEFT JOIN cic_GuideArrange g ON t.TourgroupID = g.TourgroupID,orgt_TourGroupSchedule s   
            //                                         WHERE  t.tourgroupid=s.tourgroupid AND s.TravelAgencyID={4} AND (g.ownerproviderid='{4}' OR not exists(SELECT a.TourgroupID FROM cic_GuideArrange a WHERE a.OwnerProviderID='{4}')) AND
            //                                                DATEDIFF(month,t.StartDate,'{0}')=0  AND HasGuide=1 AND (t.TourgroupState='{1}' OR t.TourgroupState='{2}' OR t.TourgroupState='{3}')", dateMonth,TeamStateEnum.分配, TeamStateEnum.审核, TeamStateEnum.终结, loginProviderId);
            string Sql = string.Format(@"SELECT DISTINCT t.HasGuide,t.TourgroupID, t.TourgroupName, t.StartDate, t.EndDate, t.OwnerA, t.StayOver, t.AdultNum, t.ChildNum, case when g.OwnerProviderID<>'{4}' then NULL else g.CiceroniName end as CiceroniName 
                                         FROM orgt_TourGroup t LEFT JOIN cic_GuideArrange g ON t.TourgroupID = g.TourgroupID,orgt_TourGroupSchedule s   
                                         WHERE  t.tourgroupid=s.tourgroupid AND s.TravelAgencyID={4} AND (g.ownerproviderid='{4}' OR t.TourgroupID not in (SELECT distinct a.TourgroupID FROM cic_GuideArrange a WHERE a.OwnerProviderID='{4}')) AND
                                                 HasGuide=1 AND (t.TourgroupState='{1}' OR t.TourgroupState='{2}' OR t.TourgroupState='{3}')   
                                                 AND DATEDIFF(month,t.StartDate,'{0}')=0 ", dateMonth, TeamStateEnum.分配, TeamStateEnum.审核, TeamStateEnum.终结, loginProviderId);
            if (szCondition != "")
                Sql += "  AND " + szCondition;

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }


        public  DataTable GetTeamGroupRowData(string tourGroupID)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT * FROM orgt_tourgroup WHERE TourGroupID = @tourGroupID";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupID);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }

        public DataTable GetTeamGroupRowDataforHotelorder(string tourGroupID)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT TourgroupName,(AdultNum+ChildNum) as GuestNum ,AccompanyNum,OwnerA,OwnerB FROM orgt_tourgroup WHERE TourGroupID = @tourGroupID";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupID);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }


        /// <summary>
        ///  获取指定团的备注信息
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        public  string GetTourGroupScheduleRemark(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [Remark] FROM orgt_TourGroup WHERE TourgroupID='{0}'", tourGroupID);

            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }
        /// <summary>
        ///  获取指定团的本地备注信息
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        public  string GetTourGroupScheduleLocalRemark(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [LocalRemark] FROM orgt_TourGroup WHERE TourgroupID='{0}'", tourGroupID);

            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }

 
        #endregion

        #region 团有关数据增、删、改
        /// <summary>
        /// 更新团行数据的计调操作状态,0、试图打开但检测到没有本地计划信息需拷贝时，置为0； 1、以首次打开为依据，将大计划的本地行程信息拷贝过来，状态置为1；2、手动方式，在本地行程计划中添加一个check框，来确定是否团已经处理过了，true=2，false=1
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="localTourgroupRowState"></param>
        /// <returns></returns>
        public  int UpdateTourGroupLocalTourgroupRowState(string tourGroupId, int localTourgroupRowState)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET LocalTourgroupRowState='{0}' WHERE TourgroupID='{1}'", localTourgroupRowState, tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// 更新团的‘宾馆备注信息’字段
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public  int UpdateTourGroupRoomRemark(string tourGroupId, string roomRemark)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET RoomRemark='{0}' WHERE TourgroupID='{1}'", roomRemark, tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 更新团的‘本地行程计划的备注’字段
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public  int UpdateTourGroupLocalRemark(string tourGroupId, string localRemark)
        {
            Database db = DatabaseFactory.CreateDatabase();
            //string sql = string.Format(@"UPDATE orgt_TourGroup SET LocalRemark='{0}' WHERE TourgroupID='{1}'", localRemark, tourGroupId);
            //DbCommand dbCommand = db.GetSqlStringCommand(sql);


            string sql = string.Format(@"UPDATE orgt_TourGroup SET [LocalRemark]=@Remark WHERE TourgroupID=@TourgroupID");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "Remark", DbType.String, localRemark);
            db.AddInParameter(dbCommand, "TourgroupID", DbType.String, tourGroupId);

            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 根据团Id更新‘预算成本、预算价格’
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="budgeTotalCost"></param>
        /// <param name="budgeTotalPrice"></param>
        /// <returns></returns>
        public int UpdateTourGroupSale(string tourGroupId, decimal saleTotalCost, decimal saleTotalPrice, string currency, decimal exchangeRate, decimal currencyAmount)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET SaleTotalCost={0},SaleTotalPrice={1},[Currency]='{2}',[ExchangeRate]={3},[CurrencyAmount]={4} WHERE TourgroupID='{5}'", saleTotalCost, saleTotalPrice, currency, exchangeRate, currencyAmount, tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// 根据团Id更新‘审核成本’
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        public  int UpdateTourGroupBudgeCost(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET BudgeTotalCost=(SELECT SUM(budget) FROM fee_expense WHERE TourgroupID='{0}' AND Status<>'{1}') WHERE TourgroupID='{0}'", tourGroupId, ExpenseStatusEnum.取消);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }



        #endregion

        #region 行程计划数据的获取
        public  DataSet GetTourGroupSchedule(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [TourGroupScheduleID], [TourgroupID], [Date], [Time], [StayOver], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [TicketType], [TravelAgencyID],[TravelAgencyName], [Reception], [Context], [Dining], [Hotel],[HotelID],[Seat],[ScheduleMethod] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'ORDER BY [Date], [Time]", tourGroupID);

            return db.ExecuteDataSet(CommandType.Text, sql);
        }
 
        #endregion

        #region 行程计划打印——团、计划、旅行社信息获取

        public  DataTable GetTourGroupScheduleForSchedulePrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [Date], [Time], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [Context],[Hotel],[Dining],[TicketType],[Seat],[ScheduleMethod] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'order by [Date],[Time]", tourGroupID);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public  DataTable GetTourGroupScheduleForChangeNotifyPrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [Date], [Time], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic],[Hotel] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'and ([Journeytype]='入境' or [Journeytype]='抵' or [Journeytype]='离境' or([Journeytype]='离'and [TrafficType]='火车') ) order by [Date],[Time]", tourGroupID);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public  DataTable GetTeamGroupDataForSchedulePrint(string tourGroupID)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT orgt_tourgroup.TourgroupID,orgt_tourgroup.TourgroupName,orgt_tourgroup.OwnerA,orgt_tourgroup.OwnerB,orgt_tourgroup.AdultNum,orgt_tourgroup.ChildNum,orgt_tourgroup.AccompanyNum,orgt_tourgroup.Remark,orgt_tourgroup.LocalRemark,orgt_tourgroup.Servicetype,orgt_tourgroup.Country,aspnet_UserInfo.Telephone,aspnet_UserInfo.Fax FROM orgt_tourgroup,aspnet_UserInfo WHERE TourGroupID = @tourGroupID and orgt_tourgroup.OwnerB = aspnet_UserInfo.[Name]";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupID);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }
        /// <summary>
        /// 按指定ID顺序返回供应商信息，用union all语句完成
        /// </summary>
        /// <param name="providerIds"></param>
        /// <returns></returns>
        public  DataTable GetTravelOrHotelInfoForPrint(string[] providerIds)
        {
            if (providerIds.Length == 0)
                return new DataTable();//返回一个空的数据表

            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder formatSql = new StringBuilder();

            for (int i = 0; i < providerIds.Length; i++)
            {
                if (providerIds[i] == "") continue;

                formatSql.AppendFormat(@" SELECT  [ProviderName], [Contact], [Telephone], [fax]  FROM bas_Provider WHERE ProviderID={0} ", providerIds[i]);

                if (i != providerIds.Length - 1 && providerIds[i + 1] != "")
                    formatSql.Append(" UNION ALL ");
            }
            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
                return null;
        }

        public  DataTable GetScheduleTravelsIDForPrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string formatSql = string.Format("select TravelAgencyID from (select min([date]) as indexfield, TravelAgencyID from orgt_TourGroupSchedule WHERE TourgroupID = '{0}' group by TravelAgencyID) as a order by a.indexfield", tourGroupID);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
                return null;
        }

        public  DataTable GetScheduleHotelIDForPrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string formatSql = string.Format("select HotelID from (select min([date]) as indexfield, HotelID from orgt_TourGroupSchedule WHERE TourgroupID = '{0}' and ScheduleMethod = '{1}'group by HotelID) as a order by a.indexfield", tourGroupID, HotelScheduleMethodEnum.自订);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
                return null;
        }

 

        #endregion

        #region 获取客人列表

        public  DataTable GetGuestListData(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string Sql = string.Format("SELECT * FROM orgt_GuestList WHERE  TourgroupID ='{0}' ORDER BY OrderID", tourGroupId);

            DbCommand dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

        #region 行程计划有关数据增、删、改


        /// <summary>
        /// 根据行程计划号，更新行程计划数据
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        public  int UpdateTourGroupSchedule(TourGroupScheduleEntity scheduleData)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroupSchedule SET
                                        [TourgroupID]='{0}',
                                        [Date]='{1}',                                      [Time]='{2}',
                                        [StayOver]={3},                                    [Journeytype]='{4}',
                                        [StartCity]='{5}',                                 [DestinationCity]='{6}',
                                        [TrafficType]='{7}',                               [Traffic]='{8}',
                                        [TicketType]='{9}',                                [TravelAgencyName]='{10}',
                                        [Reception]='{11}',                                [Context]='{12}',
                                        [Dining]='{13}',                                   [Hotel]='{14}',
                                        [TravelAgencyID]={15},                             [HotelID]={16},
                                        [Seat]='{17}',                                     [ScheduleMethod]='{18}' 
                                        WHERE TourGroupScheduleID={19}",
                                        scheduleData.TourgroupID,
                                        scheduleData.Date,
                                        scheduleData.Time,
                                        scheduleData.StayOver,
                                        scheduleData.Journeytype,
                                        scheduleData.StartCity,
                                        scheduleData.DestinationCity,
                                        scheduleData.TrafficType,
                                        scheduleData.Traffic,
                                        scheduleData.TicketType,
                                        scheduleData.TravelAgencyName,
                                        scheduleData.Reception,
                                        scheduleData.Context,
                                        scheduleData.Dining,
                                        scheduleData.Hotel,
                                        scheduleData.TravelAgencyID,
                                        scheduleData.HotelID,
                                        scheduleData.Seat,
                                        scheduleData.ScheduleMethod,
                                        scheduleData.TourGroupScheduleID
                                    );
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
 
        /// <summary>
        /// 删除指定计划号数据
        /// </summary>
        /// <param name="tourGroupScheduleID"></param>
        /// <returns></returns>
        public  int DeleteTourGroupSchedule(int tourGroupScheduleID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM orgt_TourGroupSchedule WHERE TourGroupScheduleID={0}", tourGroupScheduleID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
 
        #endregion

    }
}
